package com.wonders.hs.pd.mm.web.utils;


import com.google.common.collect.Lists;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.PrintStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;
import java.util.function.Consumer;

import static com.wonders.hs.pd.mm.web.controller.ExcelController.*;

/**
 * excel 生成器
 *
 * @author banglong
 * @date 2016/5/20
 */
public class ExcelFileGenerator {

  private static final Logger logger = LoggerFactory.getLogger(ExcelFileGenerator.class);

  private ArrayList fieldName = null; //excel标题数据集

  private ArrayList fieldData = null; //excel数据内容

  private HSSFWorkbook workBook = null;

  /**
   * 构造器
   *
   * @param fieldName 结果集的字段名
   */
  public ExcelFileGenerator(ArrayList fieldName, ArrayList fieldData) {
    this.fieldName = fieldName;
    this.fieldData = fieldData;
  }

  /**
   * 创建HSSFWorkbook对象 设置颜色
   * if (rowList.get(4).toString().contains("总计")) {
   * //                    HSSFFont font = workBook.createFont();
   * //                    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   * //                    contentStyle.setFont(font);
   * //                    contentStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
   * //                    contentStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
   * //                }
   *
   * @return HSSFWorkbook
   */
  private HSSFWorkbook createWorkbook() {

    workBook = new HSSFWorkbook();
    int rows = fieldData.size();
    int sheetNum = 0;

    int SPLIT_COUNT = this.fieldData.size() + 1;
    if (rows % SPLIT_COUNT == 0) {
      sheetNum = rows / SPLIT_COUNT;
    } else {
      sheetNum = rows / SPLIT_COUNT + 1;
    }

    for (int i = 1; i <= sheetNum; i++) {
      HSSFSheet sheet = workBook.createSheet("页面 " + i);
      HSSFRow headRow = sheet.createRow((short) 0);

      // 设置表头
      for (int j = 0; j < fieldName.size(); j++) {
        HSSFCell cell = headRow.createCell(j);
        //添加样式
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        //设置所有单元格的宽度
//                sheet.setColumnWidth((short) j, (short) 5000);
        //创建样式(使用工作本的对象创建)
        HSSFCellStyle cellStyle = workBook.createCellStyle();
        //创建字体的对象
        HSSFFont font = workBook.createFont();
        //将字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //字体大小
        font.setFontHeightInPoints((short) 12);
        //将新设置的字体属性放置到样式中
        cellStyle.setFont(font);

//                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        if (fieldName.get(j) != null) {
          cell.setCellStyle(cellStyle);
          cell.setCellValue((String) fieldName.get(j));
        } else {
          cell.setCellStyle(cellStyle);
          cell.setCellValue("-");
        }
      }

      HSSFCellStyle contentStyle = workBook.createCellStyle(); // 内容样式
      contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
      contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
      contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
      contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

      // 设置内容
      for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
        HSSFCellStyle style = contentStyle;
        HSSFRow row = sheet.createRow((short) (k + 1));
        //将数据内容放入excel单元格
        ArrayList rowList = (ArrayList) fieldData.get((i - 1)
                * SPLIT_COUNT + k);

        if (rowList.size() > 5 && rowList.get(3) != null && rowList.get(4) != null) {
          if (rowList.get(4).toString().contains("小计") || rowList.get(4).toString().contains("总计") || rowList.get(3).toString().contains("总计")) {
            style = workBook.createCellStyle();
            setFontStyle(style);
          }
        }

        for (int n = 0; n < rowList.size(); n++) {
          HSSFCell cell = row.createCell(n);

          String result = rowList.get(n) == null ? "" : rowList.get(n).toString();
          cell.setCellStyle(style);
          if (!StringUtils.isEmpty(result)) {
            cell.setCellValue(result);
          } else {
            cell.setCellValue("");
          }
        }
      }
    }
    return workBook;
  }

  private void setFontStyle(HSSFCellStyle style) {
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  //  水平居中
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
    HSSFFont font = workBook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
  }

  private void expordExcelFile(OutputStream os) throws Exception {
    workBook = createWorkbook();
    workBook.write(os);
    os.close();
  }

  /**
   * 导出 excel 文件
   * @param page_div
   * @param response
   * @param consumer
   */
  public static void exportExcelByHttpServletResponse(Map<String, Object> page_div,
                                                       HttpServletResponse response,
                                                       Consumer<OutputStream> consumer) {
    //  文件名
    String fileName = (String) page_div.get("name");
    String title = fileName;
    try {
      // 设置编码
      fileName = URLEncoder.encode(fileName, "UTF-8");
      // 输出流
      OutputStream out = response.getOutputStream();
      response.reset();
      //设置导出Excel报表的导出形式
      response.setContentType("application/vnd.ms-excel;charset=UTF-8");
      response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
      // 回掉处理
      consumer.accept(out);
      //设置输出形式
      System.setOut(new PrintStream(out));
      //刷新输出流
      out.flush();
      //关闭输出流
      out.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * @description 根据sql来导出excel文件
   */
  public static void buildExcelDocument(Map<String, Object> page_div, HttpServletResponse response,
                                        String fieldNameStr, String fieldValueNameStr, int type) {

    //初始化fieldName，fieldDate
    ArrayList fieldName = Lists.newArrayList(fieldNameStr.split(","));    //excel标题数据集
    ArrayList fieldData = getFieldData((ArrayList) page_div.get("data"), fieldValueNameStr, type);    //excel数据内容
    // 导出 excel
    exportExcelByHttpServletResponse(page_div, response, outputStream -> {
      ExcelFileGenerator efg = new ExcelFileGenerator(fieldName, fieldData);
      // 导出 excel
      try {
        efg.expordExcelFile(outputStream);
      } catch (Exception e) {
        e.printStackTrace();
      }
    });
  }

//  public HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b) {
//    HSSFPalette palette = workbook.getCustomPalette();
//    HSSFColor hssfColor = null;
//    try {
//      hssfColor = palette.findColor(r, g, b);
//      if (hssfColor == null) {
//        palette.setColorAtIndex(HSSFColor.LAVENDER.index, r, g, b);
//        hssfColor = palette.getColor(HSSFColor.LAVENDER.index);
//      }
//    } catch (Exception e) {
//      logger.error(String.valueOf(e));
//    }
//
//    return hssfColor;
//  }

//      private void setBackgroundColor(HSSFCellStyle contentStyle, String color) {
//
//        //自定义cell颜色
//        HSSFPalette palette = workBook.getCustomPalette();
//        //这里的9是索引
//        palette.setColorAtIndex((short) 9, (byte) r, (byte) g, (byte) b);
//        contentStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        contentStyle.setFillForegroundColor((short) 9);
//    }

//  //提供excel中的标题数据集
//  private ArrayList getFieldName(ArrayList data) {
//    //获取第一个map集合 用来获取列
//    if (!CollectionUtils.isEmpty(data)) {
//      Map map = (Map) data.get(0);
//      return new ArrayList(map.keySet());
//    }
//    return new ArrayList();
//  }

  //提供excel中的标题数据内容
  private static ArrayList getFieldData(ArrayList data, String fieldValueNameStr, int type) {
    ArrayList result = new ArrayList();
    ArrayList fieldValueNames = Lists.newArrayList(fieldValueNameStr.split(","));
    DecimalFormat decimalFormat = new DecimalFormat("#.00");
    switch (type) {
      case EXCEL_TYPE_COMMON: // 公共数据获取
        //循环 date 来获取数据
        getCommonData(data, result, fieldValueNames);
        break;
      case EXCEL_TYPE_SINGLE_DEPTAPPLY: // 单一科室所有账册获取
        getSingleDeptApplyData(data, result, fieldValueNames, decimalFormat);
        break;
      case EXCEL_TYPE_GOODSBOOK:  // 病区领用单查询统计
        getGoodsBookData(data, result, fieldValueNames, decimalFormat);
        break;
      case EXCEL_TYPE_PACKAGEDRAW:
        getApplyDetailData(data, result, fieldValueNames, decimalFormat);
        break;
      case EXCEL_TYPE_CARDLISTINFO:
        getCardListInfo(data, result, fieldValueNames);
        break;
    }
    return result;
  }

  private static void getCommonData(ArrayList data, ArrayList result, ArrayList fieldValueNames) {
    if (!CollectionUtils.isEmpty(data)) {
      for (Object obj : data) {
        //data中都是 map
        Map map = (Map) obj;

        //临时存放数据
        ArrayList list = new ArrayList();
        for (Object key : fieldValueNames) {
          if (map.get(key) instanceof Boolean) {
            list.add(((Boolean) map.get(key)) ? "是" : "否");
          } else {
            list.add(map.get(key));
          }
        }
        result.add(list);
      }
    }
  }

  /**
   * 领用详情
   *
   * @param data
   * @param result
   * @param fieldValueNames
   * @param decimalFormat
   */
  private static void getApplyDetailData(ArrayList data, ArrayList result, ArrayList fieldValueNames, DecimalFormat decimalFormat) {
    if (!CollectionUtils.isEmpty(data)) {
      HashMap<String, List> goods = new HashMap<>();
      double allSumCount = 0;
      double allSumMoney = 0;

      HashSet<String> deptNameArr = new HashSet<>();
      HashMap<String, Map<String, Object>> deptDataInfo = new HashMap<>();
      String oldDeptAccountName = "";
      for (int i = 0; i < data.size(); i++) {
        Map map = (Map) data.get(i);

        // 首先取得所有的数据列
        ArrayList list = new ArrayList();
        for (Object key : fieldValueNames) {
          list.add(map.get(key));
        }

        allSumCount += (double) list.get(5);
        allSumMoney += (double) list.get(6);

        String deptName = (String) list.get(0);
        if (!deptNameArr.contains(deptName)) {
          deptNameArr.add(deptName);
          HashMap<String, Object> newMap = new HashMap<>();
          newMap.put("data", new ArrayList<>());
          newMap.put("sumCount", (double) 0);
          newMap.put("sumMoney", (double) 0);
          deptDataInfo.put(deptName, newMap);
        }

        if (((List) deptDataInfo.get(deptName).get("data")).size() != 0) {
          list.set(0, "");
        }
        ((List) deptDataInfo.get(deptName).get("data")).add(list);
        deptDataInfo.get(deptName).put("sumCount", ((double) deptDataInfo.get(deptName).get("sumCount")) + (double) list.get(5));
        deptDataInfo.get(deptName).put("sumMoney", ((double) deptDataInfo.get(deptName).get("sumMoney")) + (double) list.get(6));
      }

      for (String key : deptNameArr) {
        ((List) deptDataInfo.get(key).get("data")).add(Lists.newArrayList("", "", "科室 总计", "", "", decimalFormat.format(deptDataInfo.get(key).get("sumCount")), decimalFormat.format(deptDataInfo.get(key).get("sumMoney"))));
        result.addAll((List) deptDataInfo.get(key).get("data"));
      }
      result.add(Lists.newArrayList("", "", "总计", "", "", decimalFormat.format(allSumCount), decimalFormat.format(allSumMoney)));
    }
  }

  /**
   * 病区领用单查询统计
   *
   * @param data
   * @param result
   * @param fieldValueNames
   * @param decimalFormat
   */
  private static void getGoodsBookData(ArrayList data, ArrayList result, ArrayList fieldValueNames, DecimalFormat decimalFormat) {
    if (!CollectionUtils.isEmpty(data)) {
      double allSumCount = 0;
      double allSumMoney = 0;
      String oldDeptAccountName = "";
      for (int i = 0; i < data.size(); i++) {
        Map map = (Map) data.get(i);

        // 首先取得所有的数据列
        ArrayList list = new ArrayList();
        for (Object key : fieldValueNames) {
          list.add(map.get(key));
        }

        if (i != 0 && oldDeptAccountName.equals(list.get(1))) {
          list.set(0, "");
          list.set(1, "");
        } else {
          oldDeptAccountName = (String) list.get(1);
        }

        allSumCount += (double) list.get(7);
        allSumMoney += (double) list.get(8);
        result.add(list);
      }
      result.add(Lists.newArrayList("", "", "", "总计", "", "", "", decimalFormat.format(allSumCount), decimalFormat.format(allSumMoney), ""));
    }
  }

  /**
   * 单一科室所有账册查询报表排列
   *
   * @param data
   * @param result
   * @param fieldValueNames
   * @param decimalFormat
   */
  private static void getSingleDeptApplyData(ArrayList data, ArrayList result, ArrayList fieldValueNames, DecimalFormat decimalFormat) {
    if (!CollectionUtils.isEmpty(data)) {
      double sumCount = 0;
      double sumMoney = 0;
      double allSumCount = 0;
      double allSumMoney = 0;
      String oldGoodsBook = "";
      for (int i = 0; i < data.size(); i++) {
        Map map = (Map) data.get(i);

        // 首先取得所有的数据列
        ArrayList list = new ArrayList();
        for (Object key : fieldValueNames) {
          list.add(map.get(key));
        }

        if (i == 0) {
          oldGoodsBook = (String) list.get(2);
          sumCount += (double) list.get(8);
          sumMoney += (double) list.get(9);
        } else {
          if (oldGoodsBook.equals(list.get(2)) && !org.springframework.util.StringUtils.isEmpty(list.get(2))) {
            sumCount += (double) list.get(8);
            sumMoney += (double) list.get(9);
            list.set(0, "");
            list.set(1, "");
            list.set(2, "");
          } else {
            result.add(Lists.newArrayList("", "", "", "", oldGoodsBook + "小计", "", "", "", decimalFormat.format(sumCount), decimalFormat.format(sumMoney), "", ""));
            oldGoodsBook = (String) list.get(2);
            sumCount = (double) list.get(8);
            sumMoney = (double) list.get(9);
          }
        }
        allSumCount += (double) list.get(8);
        allSumMoney += (double) list.get(9);
        result.add(list);
      }
      result.add(Lists.newArrayList("", "", "", "", oldGoodsBook + "小计", "", "", "", decimalFormat.format(sumCount), decimalFormat.format(sumMoney), "", ""));
      result.add(Lists.newArrayList("", "", "", "", "总计", "", "", "", decimalFormat.format(allSumCount), decimalFormat.format(allSumMoney), "", ""));
    }
  }

  /**
   * 固定资产数据排列
   *
   * @param data
   * @param result
   * @param fieldValueNames
   */
  private static void getCardListInfo(ArrayList data, ArrayList result, ArrayList fieldValueNames) {
    if (!CollectionUtils.isEmpty(data)) {
      for (Object obj : data) {
        //data中都是 map
        Map map = (Map) obj;

        //临时存放数据
        ArrayList list = new ArrayList();
        for (Object key : fieldValueNames) {
          if ("GoodsStatusString".equals(key.toString())) {
            switch (map.get("GoodsStatusStr").toString()) {
              case "zc":
                list.add("正常");
                break;
              case "bf":
                list.add("'报废'");
                break;
              case "db":
                list.add("'待报废'");
                break;
            }
          } else {
            if (map.get(key) instanceof Boolean) {
              list.add(((Boolean) map.get(key)) ? "是" : "否");
            } else {
              list.add(map.get(key));
            }
          }
        }
        result.add(list);
      }
    }
  }

}
